package com.src.xyzk_personal.EOL;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.src.xyzk_personal.config.Common;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbAdapter{
	private static final String TAG = "DbAdapter";
	private static final int db_version = 1;

	private DbOpenHelper dbhelper = null;
	private SQLiteDatabase m_db = null;
	private Context m_context = null;
	//构造方法
	public DbAdapter(Context context)
	{
		m_context = context;
		dbhelper = new DbOpenHelper(context);
	}
	public void close()
	{
		if(m_db != null) m_db.close();
	}
	public void open() throws SQLiteException {
		try{
			m_db = dbhelper.getWritableDatabase();
			if(Common.Debug) Log.i(TAG,"Write And Read database!");
		}
		catch(SQLiteException ex)
		{
			m_db = dbhelper.getReadableDatabase();
			if(Common.Debug) Log.e(TAG,"only read database!");
		}
	}
	public void StartAddMost()	//开始大批量事务
	{
		String create_sql = "begin transaction";
		m_db.execSQL(create_sql);
	}
	public void StopAddMost()		//结束大批量事务
	{
		String create_sql = "commit transaction";
		m_db.execSQL(create_sql);
	}

	public boolean updatemaintable(int id){
		try{
			if (m_db==null) return false;
			String updatemainSql ="update " +TableMain.Name + " set " +TableMain.update + " = '已上传'  where " + TableMain.ID +" = " +id;
			m_db.execSQL(updatemainSql);
			return true;
		}catch (Exception e){
			e.printStackTrace();
		}
		return false;
	}
	//更新主表
	public void addmainTable(ContentValues value,boolean update)
	{
		if(update)
			value.put(TableMain.update, "已上传");
		else
			value.put(TableMain.update, "未上传");
		if(m_db != null)
			m_db.insert(TableMain.Name, null, value);
	}
	//更新是否上传标记
	public boolean UpdataTableUpdate(String table,String Pvin,int Ptestnum,int update)
	{
		if(m_db == null) return false;
		ContentValues cv = new ContentValues();
		cv.put(TableMain.update, update + "");
		m_db.update(table, cv, TableMain.vin + " like ? and " + TableMain.testnum + "=?",new String[]{Pvin,"" + Ptestnum});
		//	m_db.update(table, values, whereClause, whereArgs)
		return true;
	}
	//返回测试次数,负数失败
	public int CreateTableData(String tbname)
	{
		int testnum = 0;
		if(m_db == null)
		{
			if(Common.Debug) Log.e(TAG,"Database not open!");
			return -1;
		}
		//创建主表
		String create_sql = "CREATE TABLE IF NOT EXISTS "+
				tbname		 			+"(" +
				TableData.ID  			+" integer primary key autoincrement,"+
				TableData.testnum		+" integer," +
				TableData.testcode   	+" integer," +
				TableData.context	  	+" varchar(50)," +
				TableData.testdata 		+" varchar(50)," +
				TableData.teststand 		+" varchar(50)," +
				TableData.result  		+" varchar(10)," +
				TableData.update        +" integer" +  ")";
		m_db.execSQL(create_sql);
		//先查询
		Cursor csr = null;
		csr = m_db.rawQuery("select * from " + tbname, null);
		if(csr.moveToLast())
			testnum = csr.getInt(csr.getColumnIndex(TableData.testnum));
		if(csr != null) csr.close();
		testnum ++;
		return testnum;
	}
	public void addDatatotable(String tbname,ContentValues value,boolean update)
	{
		if(update)
			value.put(TableData.update, 1);
		else
			value.put(TableData.update, 0);
		if(m_db != null)
			m_db.insert(tbname, null, value);
	}
	//查询详细数据表
	public List<Map<String, String>> queryDataTable(String table,String where,String[] whereArgs,
													String orderBy,String limit)
	{
		try{
			if(m_db == null)
			{
				if(Common.Debug) Log.e(TAG,"Database not open!");
				return null;
			}
			Cursor csr = null;
			csr = m_db.query(table, null, where, whereArgs,
					null, null, orderBy,limit);
			List<Map<String,String>> lst = new ArrayList<Map<String,String>>();
			while(csr != null && csr.moveToNext())
			{
				HashMap<String, String> map = new HashMap<String, String>();
				map.put("SHOW_ID", csr.getString(csr.getColumnIndex(TableData.testcode)));
				map.put("SHOW_CONTEXT", csr.getString(csr.getColumnIndex(TableData.context)));
				String data = csr.getString(csr.getColumnIndex(TableData.testdata));
				//if(data.length() > 0)
				for(int s = 0; s < data.length(); s ++)
				{
					byte [] databyte = data.getBytes();
					if(databyte[s] == 0)
						databyte[s] = '-';
					data = new String(databyte);
				}
				map.put("SHOW_RESULT", data);
				map.put("SHOW_TIME", csr.getString(csr.getColumnIndex(TableData.result)));
				lst.add(map);
			}
			if(csr != null) csr.close();
			return lst;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return null;
	}
	//查询主表
	public List<Map<String, Object>> querymainTable(String where,String[] whereArgs,
													String orderBy,String limit)
	{
		try{
			if(m_db == null)
			{
				if(Common.Debug) Log.e(TAG,"Database not open!");
				return null;
			}
			Cursor csr = null;
			csr = m_db.query(TableMain.Name, null, where, whereArgs,
					null, null, orderBy,limit);
			List<Map<String,Object>> lst = new ArrayList<Map<String,Object>>();
			while(csr != null && csr.moveToNext())
			{
				HashMap<String, Object> map = new HashMap<String, Object>();
				map.put(TableMain.vin, csr.getString(csr.getColumnIndex(TableMain.vin)));
				map.put(TableMain.cartype, csr.getString(csr.getColumnIndex(TableMain.cartype)));
				map.put(TableMain.timestart, csr.getString(csr.getColumnIndex(TableMain.timestart)));
				map.put(TableMain.timeend, csr.getString(csr.getColumnIndex(TableMain.timeend)));
				map.put(TableMain.result, csr.getString(csr.getColumnIndex(TableMain.result)));
				map.put(TableMain.printdata, csr.getString(csr.getColumnIndex(TableMain.printdata)));
				map.put(TableMain.device, csr.getString(csr.getColumnIndex(TableMain.device)));
				map.put(TableMain.testnum, csr.getInt(csr.getColumnIndex(TableMain.testnum)));
				map.put(TableMain.ID, csr.getInt(csr.getColumnIndex(TableMain.ID)));
				lst.add(map);
			}
			if(csr != null) csr.close();
			return lst;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return null;
	}

	//查询主表，数据管理界面使用
	public List<Map<String, String>> TablemainQuery(String m_vin,String starttime, String endtime){
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		if (m_db == null) return null;
		try{
			String querymainSql = "select "+
					TableMain.ID 			+"," +
					TableMain.vin			+"," +
					TableMain.car			+"," +
					TableMain.cartype		+"," +
					TableMain.carname		+"," +
					TableMain.station	+"," +
					TableMain.esk			+"," +
					TableMain.pin			+"," +
					TableMain.printdata		+"," +
					TableMain.device		+"," +
					TableMain.testnum		+"," +
					TableMain.timestart		+"," +
					TableMain.timeend		+"," +
					TableMain.logfileName	+"," +
					TableMain.logpath		+"," +
					TableMain.result		+"," +
					TableMain.appversion	+"," +
					TableMain.carcolor      +"," +
					TableMain.update
					+" from "+TableMain.Name + " where " +
					TableMain.vin + "='"+m_vin+"'";
			Cursor cursor = m_db.rawQuery(querymainSql,null);
			while(cursor != null && cursor.moveToNext()){
				HashMap map = new HashMap<>();
				map.put(TableMain.ID,cursor.getString(cursor.getColumnIndex(TableMain.ID)));
				map.put(TableMain.vin,cursor.getString(cursor.getColumnIndex(TableMain.vin)));
				map.put(TableMain.car,cursor.getString(cursor.getColumnIndex(TableMain.car)));
				map.put(TableMain.cartype,cursor.getString(cursor.getColumnIndex(TableMain.cartype)));
				map.put(TableMain.carname,cursor.getString(cursor.getColumnIndex(TableMain.carname)));
				map.put(TableMain.station,cursor.getString(cursor.getColumnIndex(TableMain.station)));
				map.put(TableMain.esk,cursor.getString(cursor.getColumnIndex(TableMain.esk)));
				map.put(TableMain.pin,cursor.getString(cursor.getColumnIndex(TableMain.pin)));
				map.put(TableMain.printdata,cursor.getString(cursor.getColumnIndex(TableMain.printdata)));
				map.put(TableMain.device,cursor.getString(cursor.getColumnIndex(TableMain.device)));
				map.put(TableMain.testnum,cursor.getString(cursor.getColumnIndex(TableMain.testnum)));
				map.put(TableMain.timestart,cursor.getString(cursor.getColumnIndex(TableMain.timestart)));
				map.put(TableMain.timeend,cursor.getString(cursor.getColumnIndex(TableMain.timeend)));
				map.put(TableMain.logfileName,cursor.getString(cursor.getColumnIndex(TableMain.logfileName)));
				map.put(TableMain.logpath,cursor.getString(cursor.getColumnIndex(TableMain.logpath)));
				map.put(TableMain.result,cursor.getString(cursor.getColumnIndex(TableMain.result)));
				map.put(TableMain.appversion,cursor.getString(cursor.getColumnIndex(TableMain.appversion)));
				map.put(TableMain.carcolor,cursor.getString(cursor.getColumnIndex(TableMain.carcolor)));
				map.put(TableMain.update,cursor.getString(cursor.getColumnIndex(TableMain.update)));
				list.add(map);
			}
			return list;
		} catch (Exception e){
			e.printStackTrace();
		}
		return null;
	}

	public Map<String, String> selectforIDQuery(int id){
		if (m_db == null) return null;
		try{
			String querymainSql = "select "+
					TableMain.ID 			+"," +
					TableMain.vin			+"," +
					TableMain.car			+"," +
					TableMain.cartype		+"," +
					TableMain.carname		+"," +
					TableMain.station	+"," +
					TableMain.esk			+"," +
					TableMain.pin			+"," +
					TableMain.printdata		+"," +
					TableMain.device		+"," +
					TableMain.testnum		+"," +
					TableMain.timestart		+"," +
					TableMain.timeend		+"," +
					TableMain.logfileName	+"," +
					TableMain.logpath		+"," +
					TableMain.result		+"," +
					TableMain.appversion	+"," +
					TableMain.carcolor      +"," +
					TableMain.update
					+" from "+TableMain.Name + " where " +
					TableMain.ID + "='"+id+"'";
			Cursor cursor = m_db.rawQuery(querymainSql,null);
			HashMap map = new HashMap<>();
			while(cursor != null && cursor.moveToNext()){
				map.put(TableMain.ID,cursor.getString(cursor.getColumnIndex(TableMain.ID)));
				map.put(TableMain.vin,cursor.getString(cursor.getColumnIndex(TableMain.vin)));
				map.put(TableMain.car,cursor.getString(cursor.getColumnIndex(TableMain.car)));
				map.put(TableMain.cartype,cursor.getString(cursor.getColumnIndex(TableMain.cartype)));
				map.put(TableMain.carname,cursor.getString(cursor.getColumnIndex(TableMain.carname)));
				map.put(TableMain.station,cursor.getString(cursor.getColumnIndex(TableMain.station)));
				map.put(TableMain.esk,cursor.getString(cursor.getColumnIndex(TableMain.esk)));
				map.put(TableMain.pin,cursor.getString(cursor.getColumnIndex(TableMain.pin)));
				map.put(TableMain.printdata,cursor.getString(cursor.getColumnIndex(TableMain.printdata)));
				map.put(TableMain.device,cursor.getString(cursor.getColumnIndex(TableMain.device)));
				map.put(TableMain.testnum,cursor.getString(cursor.getColumnIndex(TableMain.testnum)));
				map.put(TableMain.timestart,cursor.getString(cursor.getColumnIndex(TableMain.timestart)));
				map.put(TableMain.timeend,cursor.getString(cursor.getColumnIndex(TableMain.timeend)));
				map.put(TableMain.logfileName,cursor.getString(cursor.getColumnIndex(TableMain.logfileName)));
				map.put(TableMain.logpath,cursor.getString(cursor.getColumnIndex(TableMain.logpath)));
				map.put(TableMain.result,cursor.getString(cursor.getColumnIndex(TableMain.result)));
				map.put(TableMain.appversion,cursor.getString(cursor.getColumnIndex(TableMain.appversion)));
				map.put(TableMain.carcolor,cursor.getString(cursor.getColumnIndex(TableMain.carcolor)));
				map.put(TableMain.update,cursor.getString(cursor.getColumnIndex(TableMain.update)));
			}
			return map;
		} catch (Exception e){
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 根据主表数据查询子表的数据
	 * service层需要
	 */
	public List querysubdataforvin(String vin,int Testnum){
		try {
			if (m_db == null) return null;
			String querysubdataforvin = "select "+
					TableData.ID			+"," +
					TableData.testcode		+"," +
					TableData.context		+"," +
					TableData.testdata		+"," +
					TableData.teststand			+"," +
					TableData.result		+"," +
					TableData.testnum
					+ " from " + vin + " where " +TableData.testnum +"=" +Testnum;
			Cursor cursor=  m_db.rawQuery(querysubdataforvin,null);
			List querysubdatalist = new ArrayList<>();
			while(cursor != null && cursor.moveToNext()){
				HashMap map = new HashMap<>();
				map.put(TableData.ID,cursor.getString(cursor.getColumnIndex(TableData.ID)));
				map.put(TableData.testcode,cursor.getString(cursor.getColumnIndex(TableData.testcode)));
				map.put(TableData.context,cursor.getString(cursor.getColumnIndex(TableData.context)));
				map.put(TableData.testdata,cursor.getString(cursor.getColumnIndex(TableData.testdata)));
				map.put(TableData.teststand	,cursor.getString(cursor.getColumnIndex(TableData.teststand)));
				map.put(TableData.result,cursor.getString(cursor.getColumnIndex(TableData.result)));
				querysubdatalist.add(map);
			}
			return querysubdatalist;
		}catch (Exception e){
			e.printStackTrace();
		}
		return null;
	}
	//查询主表,返回测试次数
	public int QueryMainTestNum(String vin)
	{
		if(m_db == null)
		{
			if(Common.Debug) Log.e(TAG,"Database not open!");
			return -1;
		}
		int testnum = 0;
		Cursor csr = null;
		csr = m_db.query(TableMain.Name, null, TableMain.vin + "=?", new String[]{vin},
				null, null, null,null);
		while(csr != null && csr.moveToNext())
		{
			if(csr.getInt(csr.getColumnIndex(TableMain.testnum)) > testnum)
				testnum = csr.getInt(csr.getColumnIndex(TableMain.testnum));
		}
		if(csr != null) csr.close();
		return testnum;
	}
	private static class DbOpenHelper extends SQLiteOpenHelper{

		public DbOpenHelper(Context ctx) {
			super(ctx, Common.db_name, null, db_version);
		}
		@Override
		public void onCreate(SQLiteDatabase db) {
			//创建主表
			String create_sql = "CREATE TABLE "+
					TableMain.Name 			+"(" +
					TableMain.ID  			+" integer primary key autoincrement,"+
					TableMain.vin  			+" varchar(20)," +
					TableMain.esk			+" varchar(20)," +
					TableMain.pin			+" varchar(20)," +
					TableMain.config		+" varchar(100),"	+
					TableMain.car			+" varchar(20)," +
					TableMain.cartype   	+" varchar(30)," +
					TableMain.carcolor   	+" varchar(100)," +
					TableMain.carname   	+" varchar(30)," +
					TableMain.timestart  	+" varchar(20)," +
					TableMain.timeend  		+" varchar(20)," +
					TableMain.station  			+" varchar(20)," +
					TableMain.appversion  		+" varchar(20)," +
					TableMain.result  		+" varchar(10)," +
					TableMain.testnum  		+" integer," +
					TableMain.printdata  	+" varchar(200)," +
					TableMain.device  		+" varchar(12)," +
					TableMain.logfileName	+" varchar(20)," +
					TableMain.logpath		+" varchar(20)," +
					TableMain.update        +" varchar(20)" + ")";
			db.execSQL(create_sql);

		}
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub

		}
	}
	public interface TableMain{
		String Name = "tablemain";  //表名
		String ID = "m_id";			//id
		String vin = "m_vin";		//vin码
		String esk = "m_esk";		//esk
		String pin = "m_pin";		//pin码
		String car = "m_car";		//车型
		String config = "m_config";		//配置码
		String cartype = "m_cartype";	//车型代码
		String carcolor = "m_carcolor";	//car颜色
		String carname = "m_carname";	//carname
		String timestart = "m_timestart";	//开始时间
		String timeend = "m_timeend";		//结束时间
		String station = "m_station";		//测试工位
		String appversion = "m_appversion";		//手持终端版本号
		String result = "m_result";			//检测结果
		String testnum = "m_testnum";		//检测次数
		String printdata = "m_printdata";	//打印内容
		String device = "m_device";		//设备序列号
		String update = "m_update";		//是否上传
		String logfileName = "m_logfilename";
		String logpath = "m_logpath";
	}
	public interface TableData{
		String Name = "m_vin";	//以vin码作为存储名称
		String ID = "m_id";		//id
		String testnum = "m_testnum";	//测试序号,第几次
		String testcode = "m_testcode";	//统计号
		String context = "m_context";	//测试内容
		String testdata = "m_testdata";	//测试数据结果
		String result = "m_result";		//单项检测结果
		String teststand ="m_teststand"; //参考值
		String update	= "m_update";	//是否上传
	}
}
